package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class XtServerDao {
	public static boolean insert(XtServer bean) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "INSERT INTO xt_server (djjg,strategy,serverxh,servername,diskname,ipaddress,webaddress,serverdisk,ftpuser,ftppassword,errcheck) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)";
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, bean.getDjjg());
			pstmt.setString(i++, bean.getStrategy());
			pstmt.setLong(i++, bean.getServerXh());
			pstmt.setString(i++, bean.getServerName());
			pstmt.setString(i++, bean.getDiskName());
			pstmt.setString(i++, bean.getIpAddress());
			pstmt.setString(i++, bean.getWebAddress());
			pstmt.setString(i++, bean.getServerDisk());
			pstmt.setString(i++, bean.getFtpUser());
			pstmt.setString(i++, bean.getFtpPassword());
			pstmt.setString(i++, bean.getErrCheck());
			pstmt.setString(i++, bean.getPageCheck());
			pstmt.setString(i++, bean.getDjjgShow());
			pstmt.execute();
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}
		return true;
	}

	public static boolean update(XtServer bean) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "update xt_server set servername=?,diskname=?,ipaddress=?,serverdisk=?,webaddress=?,ftpuser=?,ftppassword=?,errcheck=?,pagecheck=?,djjgshow=? where serverxh=? and djjg=? ";
		if (bean.getStrategy() == null || "".equals(bean.getStrategy())) {

		} else {
			sql += " and strategy = ?";
		}
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setString(i++, bean.getServerName());
			pstmt.setString(i++, bean.getDiskName());
			pstmt.setString(i++, bean.getIpAddress());
			pstmt.setString(i++, bean.getServerDisk());
			pstmt.setString(i++, bean.getWebAddress());
			pstmt.setString(i++, bean.getFtpUser());
			pstmt.setString(i++, bean.getFtpPassword());
			pstmt.setString(i++, bean.getErrCheck());
			pstmt.setString(i++, bean.getPageCheck());
			pstmt.setString(i++, bean.getDjjgShow());
			pstmt.setLong(i++, bean.getServerXh());
			pstmt.setString(i++, bean.getDjjg());
			if (bean.getStrategy() == null || "".equals(bean.getStrategy())) {

			} else {
				pstmt.setString(i++, bean.getStrategy());
			}

			pstmt.execute();
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		return true;
	}

	public static boolean delete(String djjg, String strategy, long serverXh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "DELETE FROM xt_server WHERE serverxh=? and djjg=? and strategy=?";
		logger.debug(sql);
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setLong(i++, serverXh);
			pstmt.setString(i++, djjg);
			pstmt.setString(i++, strategy);
			pstmt.execute();
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		return true;
	}

	public static XtServer load(String djjg, String strategy, long serverXh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT * FROM xt_server WHERE serverXh = ? and djjg=? ";
		XtServer xtserver = null;
		if (strategy == null || "".equals(strategy)) {

		} else {
			sql += " and strategy = ?";
		}
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			pstmt.setLong(i++, serverXh);
			pstmt.setString(i++, djjg);
			if (strategy == null || "".equals(strategy)) {

			} else {
				pstmt.setString(i++, strategy);
			}

			rs = pstmt.executeQuery();
			if (!rs.next()) {
				DbClose.freePreparedStatement(pstmt);
				DbClose.freeConnection(conn);
				DbClose.freeResultSet(rs);
				return null;
			}
			xtserver = rsToBean(rs);
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		return xtserver;
	}

	public static List AddLoad() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "select djjg,servername from xt_server";
		List list1 = null;
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBeanAdd(rs));
			}
			list1 = list;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		DbClose.freeResultSet(rs);
		return list1;

	}

	private static XtServer rsToBeanAdd(ResultSet rs) throws SQLException {
		XtServer xs = new XtServer();
		xs.setServerName(rs.getString("servername"));
		xs.setDjjg(rs.getString("djjg"));
		return xs;
	}

	public static List load(long serverXh, String serverName, int page, int pageSize, String djjg) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;

		sql = "SELECT * FROM (SELECT rownum r, xt_server.* FROM xt_server WHERE 1=1 ";
		if (serverXh != 0L) {
			sql = sql + " AND serverXh LIKE ? ";
		}
		if ((serverName != null) && (serverName.length() > 0)) {
			sql = sql + " AND serverName LIKE ? ";
		}
		if (pageSize > 1) {
			sql = sql + " AND rownum <=? * ?)t WHERE t.r > (? - 1) * ? ";
		}
		String djjg56 = djjg.substring(4, 6);
		djjg56.equals("00");
		if (!djjg56.equals("00")) {
			sql = sql + "and djjg='" + djjg + "'";
		}
		logger.debug(sql);
		List list1 = null;
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if (serverXh != 0L) {
				pstmt.setString(i++, "%" + serverXh + "%");
			}
			if ((serverName != null) && (serverName.length() > 0)) {
				pstmt.setString(i++, "%" + serverName + "%");
			}
			if (pageSize > 1) {
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
				pstmt.setInt(i++, page);
				pstmt.setInt(i++, pageSize);
			}
			rs = pstmt.executeQuery();
			List list = new ArrayList();
			while (rs.next()) {
				list.add(rsToBean(rs));
			}
			list1 = list;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		DbClose.freeResultSet(rs);
		return list1;

	}

	public static int count(long serverXh, String serverName) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		ConnectDB connsyb = null;
		sql = "SELECT count(*) FROM xt_server WHERE 1=1 ";
		if (serverXh != 0L) {
			sql = sql + " AND serverXh LIKE ? ";
		}
		if ((serverName != null) && (serverName.length() > 0)) {
			sql = sql + " AND serverName LIKE ? ";
		}
		logger.debug(sql);
		int j = 0;
		try {
			connsyb = SpringContextHolder.getBean(ConnectDB.class);
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(sql);
			int i = 1;
			if (serverXh != 0L) {
				pstmt.setString(i++, "%" + serverXh + "%");
			}
			if ((serverName != null) && (serverName.length() > 0)) {
				pstmt.setString(i++, "%" + serverName + "%");
			}
			rs = pstmt.executeQuery();
			if (!rs.next()) {
				DbClose.freePreparedStatement(pstmt);
				DbClose.freeConnection(conn);
				DbClose.freeResultSet(rs);
				return 0;
			}
			j = rs.getInt(1);
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
			DbClose.freeResultSet(rs);
		}
		DbClose.freePreparedStatement(pstmt);
		DbClose.freeConnection(conn);
		DbClose.freeResultSet(rs);
		return j;
	}

	static XtServer rsToBean(ResultSet rs) throws SQLException {
		XtServer bean = new XtServer();
		bean.setDiskName(rs.getString("diskname"));
		bean.setIpAddress(rs.getString("ipaddress"));
		bean.setServerDisk(rs.getString("serverdisk"));
		bean.setServerName(rs.getString("servername"));
		bean.setServerXh(rs.getLong("serverxh"));
		bean.setDjjg(rs.getString("djjg"));
		bean.setWebAddress(rs.getString("webaddress"));
		bean.setFtpUser(rs.getString("ftpuser"));
		bean.setFtpPassword(rs.getString("ftppassword"));
		bean.setStrategy(rs.getString("strategy"));
		bean.setErrCheck(rs.getString("errcheck"));
		bean.setPageCheck(rs.getString("pagecheck"));
		bean.setDjjgShow(rs.getString("djjgshow"));
		return bean;
	}

	public static List<XtServer> getServerListForJnml(String nbxh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		List<XtServer> list = new ArrayList<XtServer>();
		String lsSql = "select a.* from xt_server a,(select distinct t.nbxh,t.jnmlbh,t.serverxh,t.djjg,s.jnmldjrq "
				+ "from DA_QYJNMLB t,da_qyzfjzmlb s where t.nbxh=? and t.nbxh=s.nbxh and t.jnmlbh=s.jnmlbh and s.zfjbz='0') b "
				+ "where a.djjg=b.djjg and a.serverxh=b.serverxh order by b.jnmldjrq";
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, nbxh);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				XtServer a = rsToBean(rs);
				list.add(a);
			}
			return list;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return list;
	}
	

	public static XtServer getServerForJnml(String nbxh, String zfjbz, String jnmlbh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		XtServer a = null;
		String lsSql = "select a.* from xt_server a,(select distinct t.nbxh,t.jnmlbh,t.serverxh,t.djjg,s.jnmldjrq "
				+ "from DA_QYJNMLB t,da_qyzfjzmlb s where t.nbxh=s.nbxh and t.jnmlbh=s.jnmlbh and  t.nbxh=? "
				+ "and t.jnmlbh=? and s.zfjbz=?) b "
				+ "where a.djjg=b.djjg and a.serverxh=b.serverxh order by b.jnmldjrq";
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);

		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, nbxh);
			pstmt.setString(2, jnmlbh);
			pstmt.setString(3, zfjbz);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				a = rsToBean(rs);
			}
			return a;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	static Logger logger = LoggerFactory.getLogger(XtServerDao.class);
	public static final String TABLE = "xt_server";


}
